﻿Imports System.Data.SqlClient
Imports System.IO
Imports System.Data
Imports System.Data.OleDb

Public Class UploadDownload
    Inherits System.Web.UI.Page
    Dim con As New DBConnect
    Dim uplPath As String = ConfigurationManager.AppSettings("uploadPath").ToString()
    Dim objCommand As New SqlCommand
    Dim objDB As New DBConnect

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'check if user signed in if not going back to login page 
        If Not Session.Item("UserIn").Equals("yes") Then
            Response.Redirect("default.aspx")
        End If

        If StatusLabel.Text = "" Then
            StatusLabel.Visible = True
        End If
    End Sub

    Protected Sub UploadButton_Click(sender As Object, e As EventArgs) Handles UploadButton.Click
        Dim fileName As String
        Dim studentList As List(Of student1)

        If (FileUploadControl.HasFile) Then
            Try
                fileName = Path.GetFileName(FileUploadControl.FileName)
                If File.Exists(uplPath + fileName) Then
                    File.Delete(uplPath + fileName)
                End If
                FileUploadControl.SaveAs(uplPath + fileName)
                studentList = ExtractStudentExcelDataV2(fileName)
                Dim results As Boolean = StudentToDatabase(studentList)
                If results = True Then
                    StatusLabel.Text = "Upload status: Success!"
                    WarningContent_ValidationSummary.Visible = True
                Else
                    StatusLabel.Text = "Upload status: Failure!"
                    WarningContent_ValidationSummary.Visible = True
                End If

            Catch ex As IOException
                'Exception
                StatusLabel.Text = ex.Message
                StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message
                WarningContent_ValidationSummary.Visible = True
            End Try

        End If
    End Sub

    Protected Sub UploadButton2_Click(sender As Object, e As EventArgs) Handles UploadButton2.Click
        Dim fileName As String
        Dim deptList As List(Of Organization)

        If (FileUploadControl2.HasFile) Then

            Try
                fileName = Path.GetFileName(FileUploadControl2.FileName)
                If File.Exists(uplPath + fileName) Then
                    File.Delete(uplPath + fileName)
                End If
                FileUploadControl2.SaveAs(uplPath + fileName)
                deptList = ExtractDeptExcelDataV2(fileName)

                Dim results As Boolean = DeptToDatabase(deptList)
                If results = True Then
                    StatusLabel.Text = "Upload status: Success!"
                    WarningContent_ValidationSummary.Visible = True
                Else
                    StatusLabel.Text = "Upload status: Failure!"
                    WarningContent_ValidationSummary.Visible = True
                End If

            Catch ex As Exception
                StatusLabel.Text = "Upload status: The file could not be uploaded." + ControlChars.NewLine + "The following error occured: " + ex.Message
                WarningContent_ValidationSummary.Visible = True
            End Try


        End If
    End Sub

    Public Function ExtractDeptExcelDataV2(fileName As String) As List(Of Organization)
        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + uplPath + fileName + "';Extended Properties='Excel 12.0;HDR=YES';"
        Dim strSQL As String = "SELECT * FROM [Sheet1$]"
        Dim excelConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(connectionString)
        excelConnection.Open()
        Dim dbCommand As OleDbCommand = New OleDbCommand(strSQL, excelConnection)
        Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(dbCommand)
        Dim ds As New DataSet
        dataAdapter.Fill(ds, "dTable")

        ' these two lines below could be used to add a gridview after uploading, if desired
        'DataGridView1.DataSource = ds.Tables("dTable").DefaultView
        'DataGridView1.DataBind()

        Dim orgList As New List(Of Organization)

        For Each Row As DataRow In ds.Tables("dTable").Rows
            Dim newOrganization As New Organization

            For Each Col As DataColumn In ds.Tables("dTable").Columns
                ' department
                newOrganization.Department = Row.Item(0).ToString()
                ' organization name
                newOrganization.Name = Row.Item(1).ToString()
                ' established term
                newOrganization.EstablishedTerm = Row.Item(2).ToString()
                ' status
                newOrganization.Status = Row.Item(3).ToString()
                orgList.Add(newOrganization)
            Next
        Next

        excelConnection.Close()
        Return orgList

    End Function

    Public Function ExtractStudentExcelDataV2(fileName As String) As List(Of student1)
        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + uplPath + fileName + "';Extended Properties='Excel 12.0;HDR=YES';"
        Dim strSQL As String = "SELECT * FROM [Sheet1$]"
        Dim excelConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(connectionString)
        excelConnection.Open()
        Dim dbCommand As OleDbCommand = New OleDbCommand(strSQL, excelConnection)
        Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(dbCommand)
        Dim ds As New DataSet
        dataAdapter.Fill(ds, "dTable")
        'DataGridView1.DataSource = ds.Tables("dTable").DefaultView
        'DataGridView1.DataBind()

        Dim studentList As New List(Of student1)
        'Try
        For Each Row As DataRow In ds.Tables("dTable").Rows
            If String.IsNullOrEmpty(Row.Item(1).ToString) Then
                ' do nothing
            Else
                Dim newStudent As New student1
                newStudent.AccessNetID = Row.Item(0).ToString()
                ' TUID
                newStudent.TuID = Row.Item(1).ToString()
                ' first name
                newStudent.FirstName = Row.Item(2).ToString()
                ' last name
                newStudent.LastName = Row.Item(3).ToString()
                ' department
                newStudent.Department = Row.Item(4).ToString()
                ' organization
                newStudent.Organization = Row.Item(5).ToString()
                ' classification
                newStudent.Classification = Row.Item(6).ToString()
                ' role
                newStudent.Role = Row.Item(7).ToString()
                ' start term
                newStudent.StartTerm = Row.Item(8).ToString()
                ' end term
                newStudent.EndTerm = Row.Item(9).ToString()
                ' comments
                newStudent.Comments = Row.Item(10).ToString()
                studentList.Add(newStudent)
            End If

            'For Each Col As DataColumn In ds.Tables("dTable").Columns
            'Next
        Next
        'Catch ex As Exception
        '    MsgBox(ex.Message)
        'End Try

        excelConnection.Close()
        Return studentList

    End Function

    'stroing information from excel to database with store procedures 
    Public Function StudentToDatabase(studentList As List(Of student1)) As Boolean

        Dim SqlConnectString As String = con.SqlConnectString
        'looping thru students in studentList
        For Each Student In studentList
            'checking if user
            Dim studentTUID As Integer
            Dim myCommand As New SqlCommand
            Dim myConnection As New SqlConnection(SqlConnectString)

            If Not String.IsNullOrEmpty(Student.Comments) Then
            Else
                Student.Comments = "N/A"
            End If
            'If Not String.IsNullOrEmpty(Student.AccessNetID) Then

            'Else
            '    Student.AccessNetID = "N/A"
            'End If
            'If Not String.IsNullOrEmpty(Student.Classification) Then

            'Else
            '    Student.Classification = "N/A"
            'End If
            'If Not String.IsNullOrEmpty(Student.Department) Then

            'Else
            '    Student.Department = "N/A"
            'End If

            'If Not String.IsNullOrEmpty(Student.Email) Then

            'Else
            '    Student.Email = "N/A"
            'End If
            'If Not String.IsNullOrEmpty(Student.EndTerm) Then

            'Else
            '    Student.EndTerm = "N/A"
            'End If
            'If Not String.IsNullOrEmpty(Student.StartTerm) Then

            'Else
            '    Student.StartTerm = "N/A"
            'End If
            'If Not String.IsNullOrEmpty(Student.FirstName) Then

            'Else
            '    Student.FirstName = "N/A"
            'End If
            'If Not String.IsNullOrEmpty(Student.LastName) Then

            'Else
            '    Student.LastName = "N/A"
            'End If
            'If Not String.IsNullOrEmpty(Student.Role) Then

            'Else
            '    Student.Role = "N/A"
            'End If

            ' insert user if doesn't already exist, returns tuID if successful (or already exists)
            myConnection.Open()
            myCommand.Connection = myConnection
            'Set the SQLCommand object's properties for executing a Stored Procedure
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.CommandText = "InsertUsers" 'Name of the Stored Procedure to execute
            Dim inputParameter As New SqlParameter("@tuID", CInt(Student.TuID))
            inputParameter.Direction = ParameterDirection.Input
            myCommand.Parameters.Add(inputParameter)
            Dim inputParameter2 As New SqlParameter("@accessNetID", CStr(Student.AccessNetID))
            inputParameter2.Direction = ParameterDirection.Input
            myCommand.Parameters.Add(inputParameter2)
            Dim inputParameter3 As New SqlParameter("@accessLevel", 1)
            inputParameter3.Direction = ParameterDirection.Input
            myCommand.Parameters.Add(inputParameter3)
            Dim inputParameter4 As New SqlParameter("@firstName", CStr(Student.FirstName))
            inputParameter4.Direction = ParameterDirection.Input
            myCommand.Parameters.Add(inputParameter4)
            Dim inputParameter5 As New SqlParameter("@lastName", CStr(Student.LastName))
            inputParameter5.Direction = ParameterDirection.Input
            myCommand.Parameters.Add(inputParameter5)
            Dim returnParameter As New SqlParameter("@new_identity", 0)
            returnParameter.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(returnParameter)

            Try
                myCommand.ExecuteScalar()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

            studentTUID = CType(myCommand.Parameters("@new_identity").Value.ToString, Integer)
            myConnection.Close()

            If studentTUID > 0 Then
                ' if successful, get department ID
                Dim studentDepartmentID As Integer
                Dim myCommand2 As New SqlCommand
                Dim myConnection2 As New SqlConnection(SqlConnectString)

                myConnection2.Open()
                myCommand2.Connection = myConnection2
                'Set the SQLCommand object's properties for executing a Stored Procedure
                myCommand2.CommandType = CommandType.StoredProcedure
                myCommand2.CommandText = "InsertDepartment" 'Name of the Stored Procedure to execute
                Dim inputParam As New SqlParameter("@DepartmentName", CStr(Student.Department))
                inputParam.Direction = ParameterDirection.Input
                myCommand2.Parameters.Add(inputParam)
                Dim returnParam As New SqlParameter("@new_identity", 0)
                returnParam.Direction = ParameterDirection.Output
                myCommand2.Parameters.Add(returnParam)

                Try
                    myCommand2.ExecuteScalar()
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

                studentDepartmentID = CType(myCommand2.Parameters("@new_identity").Value.ToString, Integer)
                myConnection2.Close()

                Dim studentOrganizationID As Integer
                If studentDepartmentID > 0 Then

                    If Not String.IsNullOrEmpty(Student.Organization) Then

                        ' if successful, get organization ID

                        Dim myCommand3 As New SqlCommand
                        Dim myConnection3 As New SqlConnection(SqlConnectString)

                        myConnection3.Open()
                        myCommand3.Connection = myConnection3
                        'Set the SQLCommand object's properties for executing a Stored Procedure
                        myCommand3.CommandType = CommandType.StoredProcedure
                        myCommand3.CommandText = "GetOrganizationID" 'Name of the Stored Procedure to execute
                        Dim inputParam1 As New SqlParameter("@organizationName", CStr(Student.Organization))
                        inputParam1.Direction = ParameterDirection.Input
                        myCommand3.Parameters.Add(inputParam1)
                        Dim returnParam1 As New SqlParameter("@new_identity", 0)
                        returnParam1.Direction = ParameterDirection.Output
                        myCommand3.Parameters.Add(returnParam1)

                        Try
                            myCommand3.ExecuteScalar()
                        Catch ex As Exception
                            MsgBox(ex.Message)
                        End Try

                        studentOrganizationID = CType(myCommand3.Parameters("@new_identity").Value.ToString, Integer)
                        myConnection3.Close()

                    Else
                        studentOrganizationID = 1
                    End If


                    If studentOrganizationID > 0 Then

                        Dim studentStartTermID As Integer
                        Dim studentEndTermID As Integer

                        ' taking care of start term and end term
                        If Not String.IsNullOrEmpty(Student.StartTerm) Then
                            ' start term
                            Dim myCommandStartTerm As New SqlCommand
                            Dim myConnectionStartTerm As New SqlConnection(SqlConnectString)

                            myConnectionStartTerm.Open()
                            myCommandStartTerm.Connection = myConnectionStartTerm
                            'Set the SQLCommand object's properties for executing a Stored Procedure
                            myCommandStartTerm.CommandType = CommandType.StoredProcedure
                            myCommandStartTerm.CommandText = "GetTermID" 'Name of the Stored Procedure to execute
                            Dim inputParamStartTerm As New SqlParameter("@TermName", CStr(Student.StartTerm))
                            inputParamStartTerm.Direction = ParameterDirection.Input
                            myCommandStartTerm.Parameters.Add(inputParamStartTerm)
                            Dim returnParamStartTerm As New SqlParameter("@TermID", 0)
                            returnParamStartTerm.Direction = ParameterDirection.Output
                            myCommandStartTerm.Parameters.Add(returnParamStartTerm)

                            Try
                                studentStartTermID = myCommandStartTerm.ExecuteScalar()
                            Catch ex As Exception
                                MsgBox(ex.Message)
                            End Try

                            'studentStartTermID = CType(myCommandStartTerm.Parameters("@TermID").Value.ToString, Integer)
                            myConnectionStartTerm.Close()

                        Else
                            studentStartTermID = 999
                        End If


                        If Not String.IsNullOrEmpty(Student.EndTerm) Then
                            ' and now end term
                            Dim myCommandEndTerm As New SqlCommand
                            Dim myConnectionEndTerm As New SqlConnection(SqlConnectString)

                            myConnectionEndTerm.Open()
                            myCommandEndTerm.Connection = myConnectionEndTerm
                            'Set the SQLCommand object's properties for executing a Stored Procedure
                            myCommandEndTerm.CommandType = CommandType.StoredProcedure
                            myCommandEndTerm.CommandText = "GetTermID" 'Name of the Stored Procedure to execute
                            Dim inputParamEndTerm As New SqlParameter("@TermName", CStr(Student.EndTerm))
                            inputParamEndTerm.Direction = ParameterDirection.Input
                            myCommandEndTerm.Parameters.Add(inputParamEndTerm)
                            Dim returnParamEndTerm As New SqlParameter("@TermID", 0)
                            returnParamEndTerm.Direction = ParameterDirection.Output
                            myCommandEndTerm.Parameters.Add(returnParamEndTerm)

                            Try
                                studentEndTermID = myCommandEndTerm.ExecuteScalar()
                            Catch ex As Exception
                                MsgBox(ex.Message)
                            End Try

                            'studentEndTermID = CType(myCommandEndTerm.Parameters("@TermID").Value.ToString, Integer)
                            myConnectionEndTerm.Close()

                        Else
                            studentEndTermID = 999
                        End If

                        ' finally proceed to doing InsertInvolvements
                        Dim studentInvolvementID As Integer
                        Dim myCommand4 As New SqlCommand
                        Dim myConnection4 As New SqlConnection(SqlConnectString)

                        ' checking if comments is emty if not do nothing if it is store n/a 
                        If Not String.IsNullOrEmpty(Student.Comments) Then

                        Else
                            Student.Comments = "N/A"
                        End If

                        myConnection4.Open()
                        myCommand4.Connection = myConnection4
                        'Set the SQLCommand object's properties for executing a Stored Procedure
                        myCommand4.CommandType = CommandType.StoredProcedure
                        myCommand4.CommandText = "InsertInvolvments" 'Name of the Stored Procedure to execute
                        Dim inParam As New SqlParameter("@tuID", studentTUID)
                        inParam.Direction = ParameterDirection.Input
                        myCommand4.Parameters.Add(inParam)
                        Dim inParam1 As New SqlParameter("@classification", CStr(Student.Classification))
                        inParam1.Direction = ParameterDirection.Input
                        myCommand4.Parameters.Add(inParam1)
                        Dim inParam2 As New SqlParameter("@departmentID", studentDepartmentID)
                        inParam2.Direction = ParameterDirection.Input
                        myCommand4.Parameters.Add(inParam2)
                        Dim inParam6 As New SqlParameter("@organizationID", studentOrganizationID)
                        inParam6.Direction = ParameterDirection.Input
                        myCommand4.Parameters.Add(inParam6)
                        Dim inParam3 As New SqlParameter("@role", CStr(Student.Role))
                        inParam3.Direction = ParameterDirection.Input
                        myCommand4.Parameters.Add(inParam3)
                        Dim inParam4 As New SqlParameter("@startTerm", studentStartTermID)
                        inParam4.Direction = ParameterDirection.Input
                        myCommand4.Parameters.Add(inParam4)
                        Dim inParam5 As New SqlParameter("@endTerm", studentEndTermID)
                        inParam5.Direction = ParameterDirection.Input
                        myCommand4.Parameters.Add(inParam5)
                        ' fix this line below - it needs to check if null
                        Dim inParam7 As New SqlParameter("@comments", Student.Comments)
                        inParam7.Direction = ParameterDirection.Input
                        myCommand4.Parameters.Add(inParam7)
                        Dim returnParameter2 As New SqlParameter("@new_identity", 0)
                        returnParameter2.Direction = ParameterDirection.Output
                        myCommand4.Parameters.Add(returnParameter2)

                        Try
                            myCommand4.ExecuteScalar()
                        Catch ex As Exception
                            MsgBox(ex.Message)
                        End Try

                        studentInvolvementID = CType(myCommand4.Parameters("@new_identity").Value.ToString, Integer)
                        myConnection4.Close()

                        If studentInvolvementID > 0 Then
                            ' success!!
                        Else
                            Continue For
                            ' StatusLabel.Visible = True
                            ' StatusLabel.Text = "Student's Involvement already exists.Please do  with TuID: " + studentTUID

                        End If
                    Else
                        MsgBox("OrganizationID < 0")
                        Return False
                    End If
                Else
                    MsgBox("DepartmentID < 0")
                    Return False
                End If
            End If
        Next
        Return True
    End Function

    Public Function DeptToDatabase(orgList As List(Of Organization)) As Boolean
        Dim SqlConnectString As String = con.SqlConnectString

        'looping thur students in studentList
        For Each Organization In orgList
            Dim result As Integer
            Dim myConnection As New SqlConnection(SqlConnectString)
            Dim myCommand As New SqlCommand()
            myConnection.Open()
            myCommand.Connection = myConnection
            'Set the SQLCommand object's properties for executing a Stored Procedure
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.CommandText = "InsertDepartment" 'Name of the Stored Procedure to execute
            Dim inputParameter As New SqlParameter("@DepartmentName", CStr(Organization.Department))
            inputParameter.Direction = ParameterDirection.Input
            myCommand.Parameters.Add(inputParameter)
            Dim returnParameter As New SqlParameter("@new_identity", 0)
            returnParameter.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(returnParameter)

            Try
                myCommand.ExecuteScalar()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

            result = CType(myCommand.Parameters("@new_identity").Value.ToString, Integer)
            myConnection.Close()

            If result > 0 Then
                ' in this case, Department ID has been acquired properly
                If Not String.IsNullOrEmpty(Organization.Name) Then
                    Dim result2 As Integer
                    Dim myCommand2 As New SqlCommand
                    Dim myConnection2 As New SqlConnection(SqlConnectString)

                    myConnection2.Open()
                    myCommand2.Connection = myConnection2
                    'Set the SQLCommand object's properties for executing a Stored Procedure
                    myCommand2.CommandType = CommandType.StoredProcedure
                    myCommand2.CommandText = "InsertOrganization" 'Name of the Stored Procedure to execute
                    Dim inputParameter2 As New SqlParameter("@organizationName", CStr(Organization.Name))
                    inputParameter2.Direction = ParameterDirection.Input
                    myCommand2.Parameters.Add(inputParameter2)
                    If Not String.IsNullOrEmpty(Organization.EstablishedTerm) Then
                        Dim inputParameter3 As New SqlParameter("@establishedTerm", CStr(Organization.EstablishedTerm))
                        inputParameter3.Direction = ParameterDirection.Input
                        myCommand2.Parameters.Add(inputParameter3)
                    Else
                        Dim inputParameter3 As New SqlParameter("@establishedTerm", "N/A")
                        inputParameter3.Direction = ParameterDirection.Input
                        myCommand2.Parameters.Add(inputParameter3)
                    End If
                    Dim inputParameter4 As New SqlParameter("@departmentID", result)
                    inputParameter4.Direction = ParameterDirection.Input
                    myCommand2.Parameters.Add(inputParameter4)
                    Dim inputParameter5 As New SqlParameter("@organizationStatus", CStr(Organization.Status))
                    inputParameter5.Direction = ParameterDirection.Input
                    myCommand2.Parameters.Add(inputParameter5)
                    Dim returnParameter2 As New SqlParameter("@new_identity", 0)
                    returnParameter2.Direction = ParameterDirection.Output
                    myCommand2.Parameters.Add(returnParameter2)

                    Try
                        myCommand2.ExecuteScalar()
                    Catch ex As Exception
                        MsgBox(ex.Message)
                    End Try

                    result2 = CType(myCommand2.Parameters("@new_identity").Value.ToString, Integer)
                    myConnection2.Close()

                    If result2 > 0 Then
                        ' do nothing
                    Else
                        Return False
                    End If
                Else
                    ' don't insert an organization - only a department

                End If
            Else
                Return False
            End If

        Next

        Return True


    End Function

    'Public Sub DisplayAccounts()
    '
    '    objCommand = New SqlCommand

    '    'Set the SQLCommand object's properties for executing a Stored Procedure
    '    objCommand.CommandType = CommandType.StoredProcedure
    '    objCommand.CommandText = "GetAccounts"  'Identify the Stored Procedure to Execute

    '    'Execute stored procedure using DBConnect object and the SQLCommand object
    '    Dim myDS As DataSet = objDB.getDataSetUsingCmdObj(objCommand)

    '    gvAccounts.DataSource = myDS
    '    gvAccounts.DataBind()
    'End Sub

    ' download for Student Excel template
    Protected Sub LinkButton2_Click(sender As Object, e As EventArgs) Handles LinkButton2.Click
        Dim fileName As String = "student_template.xlsx"
        Response.Clear()
        Response.Buffer = True
        Response.Charset = "UTF-8"
        Response.ContentEncoding = System.Text.Encoding.UTF8
        Response.ContentType = "application/ms-excel"
        Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName)
        Response.Charset = ""
        Response.TransmitFile(Server.MapPath("~/templates/student_template.xlsx"))
        Response.End()
    End Sub

    ' download for Department Excel template
    Protected Sub LinkButton3_Click(sender As Object, e As EventArgs) Handles LinkButton3.Click
        Dim fileName As String = "dept_template.xlsx"
        Response.Clear()
        Response.Buffer = True
        Response.Charset = "UTF-8"
        Response.ContentEncoding = System.Text.Encoding.UTF8
        Response.ContentType = "application/ms-excel"
        Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName)
        Response.Charset = ""
        Response.TransmitFile(Server.MapPath("~/templates/dept_template.xlsx"))
        Response.End()
    End Sub

End Class